package jsyf.com.util;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by y on 2018/8/9.
 */
public class ExportJXExcel {
    private final Logger logger = LoggerFactory.getLogger(ExportExcel.class);
    //显示的导出表的标题
    private String title;
    //导出表的列名

    private List<JSONObject> ManagerList = new ArrayList<JSONObject>();
    private HttpServletResponse  response;

    //构造方法，传入要导出的数据
    public ExportJXExcel(String title, List<JSONObject> ManagerList, HttpServletResponse  response) {
        this.ManagerList = ManagerList;
        this.title = title;
        this.response = response;
    }
    public  void creat2003Excel() throws FileNotFoundException, IOException {
        HSSFWorkbook workBook = new HSSFWorkbook();// 创建 一个excel文档对象
        HSSFSheet sheet = workBook.createSheet();// 创建一个工作薄对象
        HSSFCellStyle style = workBook.createCellStyle();// 创建样式对象
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
        HSSFRow row0=sheet.createRow(0);
        HSSFCell cell0 = row0.createCell(0);
        cell0.setCellValue(title);
        cell0.setCellStyle(style);
        for(int i=0;i<ManagerList.size();i++){
            HSSFRow row=sheet.createRow(i+1);
            for(int j=0;j<11;j++){
                HSSFCell cell = row.createCell(j);// 创建单元格
                sheet.autoSizeColumn((short)j);
                sheet.setColumnWidth(j,sheet.getColumnWidth(j)*17/10);
                switch (j){
                    case 0:
                        cell.setCellValue(ManagerList.get(i).getString("companyName"));
                        break;
                    case 1:cell.setCellValue(ManagerList.get(i).getString("roleName"));
                        break;
                    case 2:
                        if("区域".equals(ManagerList.get(i).getString("regionName"))){
                            cell.setCellValue("区域");
                        }else{
                            if(!"总经理".equals(ManagerList.get(i).getString("roleName"))){
                                cell.setCellValue(ManagerList.get(i).getString("regionName"));
                            }else{
                                cell.setCellValue(ManagerList.get(i).getString("companyName"));
                            }
                        }
                    break;
                    case 3: cell.setCellValue(ManagerList.get(i).getString("userName"));
                        break;
                    case 4: cell.setCellValue(ManagerList.get(i).getString("cycle")+"月");
                        break;
                    case 5:
                         if("0.0".equals(ManagerList.get(i).getString("targetNum"))||"".equals(ManagerList.get(i).getString("targetNum"))||"0".equals(ManagerList.get(i).getString("targetNum"))){
                            cell.setCellValue("-");
                        }else{
                             cell.setCellValue(ManagerList.get(i).getString("targetNum"));
                         }
                        break;
                    case 6:
                        if("0.0".equals(ManagerList.get(i).getString("finishNum"))||"".equals(ManagerList.get(i).getString("finishNum"))||"0".equals(ManagerList.get(i).getString("finishNum"))){
                            cell.setCellValue("-");
                        }else{
                            cell.setCellValue(ManagerList.get(i).getString("finishNum"));
                        }
                        break;
                    case 7:
                        if("0".equals(ManagerList.get(i).getString("saleReward"))||"".equals(ManagerList.get(i).getString("saleReward"))){
                            cell.setCellValue("-");
                        }else{
                            cell.setCellValue(ManagerList.get(i).getString("saleReward"));
                        }
                    break;
                    case 8:
                        if("0".equals(ManagerList.get(i).getString("salePunishment"))||"".equals(ManagerList.get(i).getString("salePunishment"))){
                            cell.setCellValue("-");
                        }else{
                            cell.setCellValue(ManagerList.get(i).getString("salePunishment"));
                        }
                        break;
                    case 9:
                        if("奖惩合计".equals(ManagerList.get(i).getString("total"))){
                            cell.setCellValue("奖惩合计");
                        }else{
                            if(0==(Integer.parseInt(ManagerList.get(i).getString("salePunishment"))+Integer.parseInt(ManagerList.get(i).getString("saleReward")))){
                                cell.setCellValue("-");
                            }else{
                                cell.setCellValue(Integer.parseInt(ManagerList.get(i).getString("salePunishment")) + Integer.parseInt(ManagerList.get(i).getString("saleReward")));
                            }
                        }
                        break;
                    case 10:
                        if("目标完成情况".equals(ManagerList.get(i).getString("remark"))){
                            cell.setCellValue("目标完成情况");
                        }else if(Integer.parseInt(ManagerList.get(i).getString("saleReward"))+Integer.parseInt(ManagerList.get(i).getString("salePunishment"))>0){
                            cell.setCellValue("已完成");
                        }else{
                            cell.setCellValue("未完成");
                        }
                        break;
                }
                cell.setCellStyle(style);
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
        if(workBook!=null){
            try
            {
                // excel 表文件名
                String fileName = title + ".xls";
                String fileName11 = URLEncoder.encode(fileName,"UTF-8");
                String headStr = "attachment; filename=\"" + fileName11 + "\"";
                response.setContentType("APPLICATION/OCTET-STREAM");
                response.setHeader("Content-Disposition", headStr);
                OutputStream out = response.getOutputStream();
                workBook.write(out);
                out.flush();
                out.close();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }

        }
       /*FileOutputStream os = new FileOutputStream("甘肃预测.xls");
       workBook.write(os);// 将文档对象写入文件输出流
        os.close();// 关闭文件输出流
        System.out.println("创建成功 office 2003 excel");*/
    }


    /**
     * 对外提供读取excel 的方法
     */
}
